const {con} = require('../db/db');

async function getWorkList(page) {
    const sql = `select * from works order by id limit ${(page-1)*10},10;`;

    const total = `select * from works;`;

    let [rows] = await con.execute(sql);

    let [totals] = await con.execute(total);

    return {data:rows,total:totals.length}
}

async function addWork(name,pathway,address,telephone) {
    const sql = `insert into logistics(name,pathway,address,telephone) values("${name}","${pathway}","${address}","${telephone}");`;

    await con.execute(sql);
}

async function editWork(name,pathway,address,telephone,id) {
    const sql = `update logistics set name="${name}",pathway="${pathway}",address="${address}",telephone="${telephone}" where id=${id};`;

    await con.execute(sql);
}

async function delWorkById(id) {
    const sql = `delete from logistics where id=${id};`;

    await con.execute(sql);
}

async function changeTop(id,top) {
    const sql = `update logistics set top=${top} where id=${id};`;

    await con.execute(sql);
}

async function searchWord(search) {
    const sql = `select * from logistics where name like '%${search}%';`;

    let [rows] = await con.execute(sql);

    return rows
}

//获取作品详情页面
async function getDetail(id) {
    const sql = `select * from works where id=${id};`;
    
    let [works] = await con.execute(sql);

    const sql_user = `select username,avatar from user where id=${works[0].userid};`;

    let [user] = await con.execute(sql_user);

    return {...works[0],username:user[0].username,avatar:user[0].avatar};
}

async function getMyWorks(id) {
    const sql = `select * from works where userid=${id};`;
    
    let [rows] = await con.execute(sql);

    return rows;

}

async function getMyStar(userid,workid) {
    const sql = `select * from work_collect where userid=${userid} and workid=${workid};`

    let [rows] = await con.execute(sql);

    if(rows.length > 0) {
        return {code:200}
    }else{
        return {code:500}
    }
}

async function getMyLike(userid,workid) {
    const sql = `select * from work_like where userid=${userid} and workid=${workid};`

    let [rows] = await con.execute(sql);

    if(rows.length > 0) {
        return {code:200}
    }else{
        return {code:500}
    }
}

async function addStar(userid,workid,state) {
    if(state === false) {
        const sql_update = `update works set stars=stars+1 where id=${workid};`;
        await con.execute(sql_update);
        const sql_insert = `insert into work_collect(workid,userid,create_time) values(${workid},${userid},"${new Date().toLocaleString()}");`;
        await con.execute(sql_insert);

        return {code:200,state:'stared'};
    }else{
        const sql_update = `update works set stars=stars-1 where id=${workid};`;
        await con.execute(sql_update);
        const sql_delete = `delete from work_collect where workid=${workid} and userid=${userid};`;
        await con.execute(sql_delete);

        return {code:200,state:'unstared'};
    }
}

async function addLike(userid,workid,state) {
    if(state === false) {
        const sql_update = `update works set likes=likes+1 where id=${workid};`;
        await con.execute(sql_update);
        const sql_insert = `insert into work_like(workid,userid,create_time) values(${workid},${userid},"${new Date().toLocaleString()}");`;
        await con.execute(sql_insert);

        return {code:200,state:'liked'};
    }else{
        const sql_update = `update works set likes=likes-1 where id=${workid};`;
        await con.execute(sql_update);
        const sql_delete = `delete from work_like where workid=${workid} and userid=${userid};`;
        await con.execute(sql_delete);

        return {code:200,state:'unliked'};
    }
}

module.exports={
    getWorkList,
    addWork,
    editWork,
    delWorkById,
    changeTop,
    searchWord,
    getDetail,
    getMyWorks,
    getMyStar,
    getMyLike,
    addStar,
    addLike
}